package com.gitee.zzh.sqllineage.transform;

import com.gitee.zzh.sqllineage.util.SqlStringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

public class ReplaceText extends SqlStringUtils {

    public static String replaceCommon(String text) {
        text = text.replace("\r\n", "\n");
        String[] lines = text.split("\n");
        StringBuilder stringBuilder = new StringBuilder();
        for (String line : lines) {
            if(isBlank(line)) {
                continue;
            }
            if(!line.contains("'")) {
                stringBuilder.append(line).append("\n");
                continue;
            }
            if(getCharCount(line, "'") % 2 == 1) {
                System.err.println("getCharCount(line, \"'\") % 2 == 1 : " + line);
                return text;
            }
            List<String> list = subStr(line, "'");
            for (String sonStr : list) {
                line = line.replace(sonStr, "''");
            }
            stringBuilder.append(line).append("\n");
        }
        text = stringBuilder.toString().toUpperCase();
        text = replaceSpace(text, "\\", "\t");
        StringBuilder stringBuilder1 = new StringBuilder();
        String[] lines1 = text.split("\n");
        for (String line : lines1) {
            if (isBlank(line) || line.trim().startsWith("--")) {
                continue;
            }
            if(!line.contains("--")) {
                stringBuilder1.append(line).append("\n");
                continue;
            }
            line = line.substring(0, line.indexOf("--")).trim();
            stringBuilder1.append(line).append("\n");
        }
        text = stringBuilder1.toString().replace("\n", " ");
        text = replaceSpace(text);
        text = text.replace("CREATE TABLE IF NOT EXISTS ", "CREATE TABLE ")
                   .replace("MERGE INTO ", "INSERT INTO ")
                   .replace(")UNION ", ") UNION ")
                   .replace(" UNION(", " UNION (")
                   .replace(") UNION (", ") UNION ALL (")
                   .replace(" UNION ", " UNION ALL ")
                   .replace(" UNION ALL ALL ", " UNION ALL ");
        text = replaceSpace(text, " USING ", " DISTINCT ");
        text = trimSpace(text, ",", ".", "=", ";", "(", ")");
        StringBuilder stringBuilder2 = new StringBuilder();
        for (String textPart : text.split(";")) {
            String textTrim = textPart.trim();
            if(textTrim.contains("/*")) {
                textTrim = textTrim.substring(textTrim.indexOf("/*"), textTrim.lastIndexOf("*/") + "*/".length());
            }
            if (textTrim.startsWith("CREATE TABLE ")) {
                String tableName = textTrim.substring("CREATE TABLE ".length()).trim();
                tableName = substringBySymbol(tableName, " ", "(", ")");
                if (textTrim.startsWith("CREATE TABLE " + tableName + "(SELECT ")) {
                    String left = textTrim.substring(0, textTrim.indexOf("(SELECT ") + "(SELECT ".length());
                    String left1 = textTrim.substring(0, textTrim.indexOf("(SELECT ")) + " AS SELECT ";
                    textTrim = textTrim.replace(left, left1);
                    textTrim = textTrim.substring(0, textTrim.lastIndexOf(")"));
                }
                if (textTrim.startsWith("CREATE TABLE " + tableName + " AS(SELECT ")) {
                    String left = textTrim.substring(0, textTrim.indexOf(" AS(SELECT ") + " AS(SELECT ".length());
                    String left1 = textTrim.substring(0, textTrim.indexOf(" AS(SELECT ")) + " AS SELECT ";
                    textTrim = textTrim.replace(left, left1);
                    textTrim = textTrim.substring(0, textTrim.lastIndexOf(")"));
                }
                if (textTrim.startsWith("CREATE " + tableName + " SELECT ")) {
                    textTrim = textTrim.replace("CREATE TABLE " + tableName + " SELECT ", "CREATE TABLE " + tableName + " AS SELECT ");
                }
            }
            if(textTrim.startsWith("INSERT ") && !textTrim.startsWith("INSERT INTO ")) {
                textTrim = "INSERT INTO " + textTrim.substring("INSERT ".length());
            }
            if(textTrim.contains(" INSERT INTO ") && !textTrim.startsWith("INSERT INTO ")) {
                textTrim = textTrim.substring(textTrim.indexOf("INSERT INTO "));
            }
            if(textTrim.contains(" CREATE TABLE ") && !textTrim.startsWith("CREATE TABLE ")) {
                textTrim = textTrim.substring(textTrim.indexOf("CREATE TABLE "));
            }
            if(textTrim.contains(" UPDATE ") && textTrim.contains(" SET ") && !textTrim.startsWith("UPDATE ")) {
                textTrim = textTrim.substring(textTrim.indexOf("UPDATE "));
            }
            if (textTrim.contains(")UNION ALL(SELECT ")) {
                textTrim = textTrim.replace(")UNION ALL(SELECT ", " UNION ALL SELECT ");
            }
            if (textTrim.contains(" UNION ALL(SELECT ")) {
                List<String> sonTextList = bracketSubStr(textTrim, " UNION ALL(SELECT ");
                for (String sonStr : sonTextList) {
                    String targetSonStr = sonStr.replace(" UNION ALL(SELECT ", " UNION ALL SELECT ");
                    int index = targetSonStr.lastIndexOf(")");
                    targetSonStr = targetSonStr.substring(0, index);
                    textTrim = textTrim.replace(sonStr, targetSonStr);
                }
            }
            if (textTrim.contains(")UNION ALL SELECT ")) {
                List<String> sonTextList = getEndBracketSubStr(textTrim, ")UNION ALL SELECT ", "(", ")");
                for (String sonStr : sonTextList) {
                    String targetSonStr = sonStr.replace(")UNION ALL SELECT ", " UNION ALL SELECT ");
                    int index = targetSonStr.indexOf("(");
                    targetSonStr = targetSonStr.substring(index + 1);
                    textTrim = textTrim.replace(sonStr, targetSonStr);
                }
            }
            if (textTrim.contains("((SELECT ")) {
                List<String> sonTextList = bracketSubStr(textTrim, "((SELECT ");
                for (String sonStr : sonTextList) {
                    String targetSonStr = sonStr.substring(1, sonStr.length() - 1);
                    textTrim = textTrim.replace(sonStr, targetSonStr);
                }
            }
            if(textTrim.contains(" FROM ") || textTrim.contains(" JOIN ")) {
                Set<String> tableSet = getAllTableBySelect(textTrim);
                for (String tableName : tableSet) {
                    if(textTrim.contains(" " + tableName + ".")) {
                        textTrim = textTrim.replace(" " + tableName + ".", " ");
                    }
                    if(textTrim.contains("," + tableName + ".")) {
                        textTrim = textTrim.replace("," + tableName + ".", ",");
                    }
                }
            }
            stringBuilder2.append(textTrim).append(";");
        }
        return stringBuilder2.toString();
    }

    public static String getReplaceComma(String text) {
        String[] textArr = text.split(";");
        StringBuilder stringBuilder = new StringBuilder();
        for (String textPart : textArr) {
            if(!textPart.contains("SELECT ") || !textPart.contains(" FROM ")) {
                stringBuilder.append(textPart).append(";");
                continue;
            }
            List<String> textList = getUnienSelectList(textPart, false);
            List<String> unionList = new ArrayList<>();
            for (String sqlText : textList) {
                String middle = sqlText.substring(sqlText.indexOf("SELECT "), sqlText.indexOf(" FROM "));
                String middle1 = replaceFunc(middle);
                if(!middle.equals(middle1)) {
                    sqlText = sqlText.replace(middle, middle1);
                }
                unionList.add(sqlText.trim());
            }
            stringBuilder.append(String.join(" UNION ALL ", unionList)).append(";");
        }
        return stringBuilder.toString();
    }
}
